﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;
namespace QLCN
{
    public partial class FrmDmPhuong : Form
    {
        public static string TenTinh = string.Empty;
        public static string TenHuyen = string.Empty;
        private SqlDataAdapter sqlDataAdapter;
        private SqlConnection sqlConnection;
        private SqlCommand sqlCommand;
        private SqlDataReader sqlDataReader;
        private DataSet sqlDataSet;
        private DataTable sqlTable;
        private String Maso;
        private String Matinh = "";
        private String MaHuyen = "";
        private string ServerName = Chung.ServerName;
        private string DatabaseName = Chung.DatabaseName;
        private string UserDatabase = Chung.UserDatabase;
        private string PassWordDatabase = Chung.PasswordDatabase;
        private string ChuoiKetNoi = "";
        private string chedo = ""; 

        public FrmDmPhuong()
        {
            InitializeComponent();
        }

        private void FrmDmPhuong_Load(object sender, EventArgs e)
        {
            ChuoiKetNoi = "Data Source = " + ServerName + ";" +
                          "Initial Catalog = " + DatabaseName + ";" +
                          "persist security info = true;" +
                          "User Id=" + UserDatabase +
                          "; Password='" + PassWordDatabase +
                          "'; Connect Timeout =50";
            //mo ket noi
            sqlConnection = new SqlConnection(ChuoiKetNoi);
            sqlConnection.Open();

            try
            {
                string dulieu = "", table = "";

                table = " DmTinh order by TenTinh ";
                dulieu = " Matinh,TenTinh ";

                sqlCommand = new SqlCommand("spDislay", sqlConnection);
                sqlCommand.CommandType = CommandType.StoredProcedure;

                //lấy giá trị vào biến    
                sqlCommand.Parameters.AddWithValue("@dulieu", dulieu.Trim());
                sqlCommand.Parameters.AddWithValue("@Table", table.Trim());

                //thực thi truy van 
                sqlCommand.ExecuteNonQuery();

                sqlDataAdapter = new SqlDataAdapter(sqlCommand);
                sqlDataSet = new DataSet();
                sqlDataAdapter.Fill(sqlDataSet, "DmTinh");

                sqlTable = sqlDataSet.Tables[0];

                //chuyen len luoi
                cboMaTinh.DataSource = sqlTable;
                cboMaTinh.DisplayMember = "TenTinh";
                cboMaTinh.ValueMember = "MaTinh";

                sqlDataAdapter.Dispose();
                sqlCommand.Dispose();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message, "Loi!");
            }

            Matinh = "";
            cboMaTinh.Text = "";
            Display(cboHuyen.Text);

           Setcontrol(false);
        }
        private void FrmDMHuyen_KeyDown(object sender, KeyEventArgs e)
        {
           
            
        }

        private void Setcontrol(bool T)
        {
            if (T)
            {
                txtMaPhuong.Enabled = true;
                txtTenPhuong.Enabled = true;
                txtTenTV.Enabled = true;
                btnLuu.Enabled = true;

            }
            else
            {
                txtMaPhuong.Enabled = false;
                txtTenPhuong.Enabled = false;
                txtTenTV.Enabled = false;
                btnLuu.Enabled = false;
            }
        }

        private void Display(string ten1)
        {
            string dulieu = "", table = "";

            try
            {
                if (cboHuyen.Text.Trim() == "")
                {
                    table = " DmDiaphuong  where MaTinh =  '" + Matinh.Trim() + "' order by TenPhuong ";
                }
                else
                {
                    table = " DmDiaphuong  Where  MaHuyen = '" + MaHuyen.Trim() + "' order by TenPhuong ";
                }

                dulieu = " IdPhuong, MaPhuong,TenPhuong, TenVT ";

                sqlCommand = new SqlCommand("spDislay", sqlConnection);
                sqlCommand.CommandType = CommandType.StoredProcedure;

                //lấy giá trị vào biến    
                sqlCommand.Parameters.AddWithValue("@dulieu", dulieu.Trim());
                sqlCommand.Parameters.AddWithValue("@Table", table.Trim());

                //thực thi truy van 
                sqlCommand.ExecuteNonQuery();

                sqlDataAdapter = new SqlDataAdapter(sqlCommand);
                sqlDataSet = new DataSet();
                sqlDataAdapter.Fill(sqlDataSet, "DmDiaPhuong");

                sqlTable = sqlDataSet.Tables[0];

                //chuyen len luoi
                dataGridView1.DataSource = sqlTable;
                dataGridView1.AutoGenerateColumns = false;

                sqlDataAdapter.Dispose();
                sqlCommand.Dispose();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message, "Loi!");
            }

        }

        private void cboHuyen_SelectedIndexChanged(object sender, EventArgs e)
        {
            if (btnThem.Enabled == false)
            {
                txtMaPhuong.Text = MaHuyen.Trim();
            }

            MaHuyen = cboHuyen.SelectedValue.ToString().Trim();            
            Display(cboHuyen.Text);          
        }

        private void cboMaTinh_SelectedIndexChanged(object sender, EventArgs e)
        {      
           
            try
            {
                string dulieu = "", table = "";

                table = " DmHuyen where MaTinh = '" + cboMaTinh.SelectedValue.ToString().Trim() + "' order by TenHuyen";
                dulieu = " MaHuyen,TenHuyen ";

                sqlCommand = new SqlCommand("spDislay", sqlConnection);
                sqlCommand.CommandType = CommandType.StoredProcedure;

                //lấy giá trị vào biến    
                sqlCommand.Parameters.AddWithValue("@dulieu", dulieu.Trim());
                sqlCommand.Parameters.AddWithValue("@Table", table.Trim());

                //thực thi truy van 
                sqlCommand.ExecuteNonQuery();

                sqlDataAdapter = new SqlDataAdapter(sqlCommand);
                sqlDataSet = new DataSet();
                sqlDataAdapter.Fill(sqlDataSet, "DmHuyen");

                sqlTable = sqlDataSet.Tables[0];

                //chuyen len luoi
                cboHuyen.DataSource = sqlTable;
                cboHuyen.Text = "";
                cboHuyen.DisplayMember = "TenHuyen";
                cboHuyen.ValueMember = "MaHuyen";

                sqlDataAdapter.Dispose();
                sqlCommand.Dispose();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message, "Loi!");
            }

            Matinh = cboMaTinh.SelectedValue.ToString().Trim();
            
        }

        private void dataGridView1_CellClick(object sender, DataGridViewCellEventArgs e)
        {
            int row = e.RowIndex;
            Maso = dataGridView1.CurrentRow.Cells[0].Value.ToString();
            txtMaPhuong.Text = dataGridView1.CurrentRow.Cells[1].Value.ToString();
            txtTenPhuong.Text = dataGridView1.CurrentRow.Cells[2].Value.ToString();
            txtTenTV.Text = dataGridView1.CurrentRow.Cells[3].Value.ToString();
            txtMaPhuong.Enabled = true;
            txtTenPhuong.Enabled = true;
            txtTenTV.Enabled = true;
        }

        private void Moi()
        {
            txtMaPhuong.Text = MaHuyen.Trim();
            txtTenPhuong.Text = "";
            txtTenTV.Text = "";
            txtMaPhuong.Focus();
            btnThem.Enabled = false;
            btnSua.Enabled = true;
            btnLuu.Enabled = true;
            Setcontrol(true);
            chedo = "them";
        }

        private void sua()
        {
            btnThem.Enabled = true;
            Setcontrol(true);
            btnSua.Enabled = false;
            btnLuu.Enabled = true;
            btnLuu.Focus();
            chedo = "sua";
        }

        private void BntLui_Click(object sender, EventArgs e)
        {
            sqlConnection.Close();
            this.Close();
        }

        private void btnThem_Click(object sender, EventArgs e)
        {
            Moi();
        }

        private void btnSua_Click(object sender, EventArgs e)
        {
            sua();
        }

        private void btnLuu_Click(object sender, EventArgs e)
        {
            luu();
        }

        private void btnXoa_Click(object sender, EventArgs e)
        {
            xoa();
        }


        private void luu()
        {
            try
            {
                string table = "", dulieu = "", truongdl = "", dieukien = "";
                table = "DmDiaPhuong";

                switch (chedo)
                {

                    case "them":
                        {
                            truongdl = " MaPhuong, TenPhuong, TenVT, MaTinh, MaHuyen ";

                            if ((txtMaPhuong.Text != "") && (txtTenPhuong.Text != ""))
                            {
                                dulieu = " '" + txtMaPhuong.Text.Trim() + "',N'" + txtTenPhuong.Text.Trim() + "', N'" + txtTenTV.Text.Trim() + "', '" + Matinh.Trim() + "', '" + MaHuyen.Trim() + "' ";

                                sqlCommand = new SqlCommand("spNhapmoi", sqlConnection);
                                sqlCommand.CommandType = CommandType.StoredProcedure;
                                //lấy giá trị vào biến    
                                sqlCommand.Parameters.AddWithValue("@Table", table.Trim());
                                sqlCommand.Parameters.AddWithValue("@TruongDl", truongdl.Trim());
                                sqlCommand.Parameters.AddWithValue("@dulieu", dulieu.Trim());

                                //thực thi truy van 
                                sqlCommand.ExecuteNonQuery();
                                sqlCommand.Dispose();

                                Display(cboMaTinh.Text);
                                Setcontrol(false);
                                btnThem.Enabled =true ;
                                btnSua.Enabled = true;
                                txtMaPhuong.Text = "";
                                txtTenPhuong.Text = "";
                                txtTenTV.Text = "";
                                txtMaPhuong.Focus();

                            }

                            else
                            {
                                MessageBox.Show("Bạn nhập chưa đầy đủ dữ liệu", "Thông báo");
                                txtMaPhuong.Focus();
                            }
                            break;
                        }

                    case "sua":
                        {

                            if ((txtMaPhuong.Text != "") && (txtTenPhuong.Text != ""))
                            {
                                if (MaHuyen.Trim() == "")
                                {
                                    dulieu = " MaPhuong ='" + txtMaPhuong.Text.Trim() + "', TenPhuong = N'" + txtTenPhuong.Text.Trim() + "', TenVT = N'" + txtTenTV.Text.Trim() + "', MaTinh = '" + Matinh.Trim() + "' ";
                                }
                                else
                                {
                                    dulieu = " MaPhuong ='" + txtMaPhuong.Text.Trim() + "', TenPhuong = N'" + txtTenPhuong.Text.Trim() + "', TenVT = N'" + txtTenTV.Text.Trim() + "', MaTinh = '" + Matinh.Trim() + "', MaHuyen = '" + MaHuyen.Trim() + "' ";
                                }
                            }

                            dieukien = "IdPhuong = '" + Convert.ToInt16(Maso.Trim()) + "'";

                            
                            sqlCommand = new SqlCommand("spCapnhat", sqlConnection);
                            sqlCommand.CommandType = CommandType.StoredProcedure;

                            //lấy giá trị vào biến 
                            sqlCommand.Parameters.AddWithValue("@Table", table.Trim());
                            sqlCommand.Parameters.AddWithValue("@dulieu", dulieu.Trim());
                            sqlCommand.Parameters.AddWithValue("@Dieukien", dieukien.Trim());

                            // MessageBox.Show("Có lỗi cập nhật : "  + table + dulieu + dieukien , "Thông báo :");

                            //thực thi truy van 
                            sqlCommand.ExecuteNonQuery();
                            sqlCommand.Dispose();

                            Display(cboMaTinh.Text);
                            Setcontrol(false);
                            btnLuu.Enabled = true;
                            btnSua.Enabled = true;
                            btnThem.Enabled = true;
                            btnSua.Enabled = true;
                            txtMaPhuong.Text = "";
                            txtTenPhuong.Text = "";
                            BntLui.Focus();
                            break;
                        } 
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show("Có lỗi cập nhật : " + ex.Message, "Thông báo :");
            }
        }

        private void cboHuyen_TextChanged(object sender, EventArgs e)
        {
            Display(cboHuyen.Text);
        }

        private void cboHuyen_Enter(object sender, EventArgs e)
        {
            Display(cboHuyen.Text);

        }

        private void xoa()
        {
            string table = "", dieukien = "";
            if (!string.IsNullOrEmpty(Maso))
            {
                if (MessageBox.Show("Bạn muốn xóa Phường/ Xã : " + txtTenPhuong.Text.Trim() + " ? ", "Thông báo", MessageBoxButtons.YesNo, MessageBoxIcon.Question) == DialogResult.Yes)
                {
                    table = " DmDiaPhuong";
                    dieukien = " IdPhuong = '" + Maso.Trim() + "'";

                    sqlCommand = new SqlCommand("spXoa", sqlConnection);
                    sqlCommand.CommandType = CommandType.StoredProcedure;

                    //lấy giá trị vào biến                                
                    sqlCommand.Parameters.AddWithValue("@Table", table.Trim());
                    sqlCommand.Parameters.AddWithValue("@Dieukien", dieukien.Trim());

                    //thực thi truy van                                     
                    sqlCommand.ExecuteNonQuery();
                    sqlCommand.Dispose();

                    Display(cboMaTinh.Text);
                    Setcontrol(false);
                    btnSua.Enabled = true;
                    btnThem.Enabled = true;
                    btnLuu.Enabled = false;

                    txtMaPhuong.Text = "";
                    txtTenPhuong.Text = "";
                    txtTenTV.Text = "";
                    txtMaPhuong.Focus();
                }
            }
        }

        private void FrmDmPhuong_KeyDown(object sender, KeyEventArgs e)
        {
            switch (e.KeyValue)
            {

                case 113:
                    luu();
                    break;

                case 27:
                    {
                        sqlConnection.Close();
                        this.Close();
                    }

                    break;

                case 114:
                    {
                        Moi();
                        break;
                    }

                case 115:
                    {
                        sua();
                        break;
                    }
                case 117:
                    {
                        xoa();
                        break;
                    }

                case 112:
                    {
                        //Tro giup
                        break;
                    }

            }
        }

        private void btnGoiformT_Click(object sender, EventArgs e)
        {
            Form frm = new FrmDMTinh();
            frm.ShowDialog();

            if (!string.IsNullOrEmpty(TenTinh)) cboMaTinh.Text = TenTinh;
        }

        private void frmGoiformH_Click(object sender, EventArgs e)
        {
            //lấy mã tỉnh cho from Huyen
            FrmDMHuyen.TenTinh = cboMaTinh.Text.Trim();

            Form frm = new FrmDMHuyen();
            frm.ShowDialog();            
            if (!string.IsNullOrEmpty(TenHuyen)) cboHuyen.Text = TenHuyen;

            
        }
    }
}
